from sqlalchemy import (
    create_engine,
    MetaData,
    Table,
    Column,
    Integer,
    Numeric,
    String,
    ForeignKey,
    or_,
    and_,
)

from sqlalchemy.orm import relationship, declarative_base
from pprint import pprint
from sqlalchemy.ext.hybrid import hybrid_property

engine = create_engine("mysql+pymysql://root:root@localhost/jobs")
try:
    # 创建声明式的基类，所有的模型类都要继承自这个基类
    Base = declarative_base()

    # 定义User模型类
    class User(Base):
        # 指定数据库的表名为users
        __tablename__ = "users"
        id = Column(Integer, primary_key=True)
        name = Column(String(50))
        fullname = Column(String(50))
        # 定义与Address表一对多的关系，back_populates指定反向引用
        addresses = relationship("Address", back_populates="user")

        # 使用hybrid_property装饰器定义混合属性
        @hybrid_property
        def description(self):
            email_addresses = ",".join(
                [address.email_address for address in self.addresses]
            )
            return f"{self.fullname} {email_addresses}"

    class Address(Base):
        # 指定数据库的表名为users
        __tablename__ = "addresses"
        id = Column(Integer, primary_key=True)
        email_address = Column(String(50), nullable=False)
        user_id = Column(Integer, ForeignKey("users.id"))
        user = relationship("User", back_populates="addresses")

    class Account(Base):
        # 指定数据库的表名为users
        __tablename__ = "account"
        id = Column(Integer, primary_key=True)
        user_id = Column(Integer, ForeignKey("users.id"))
        balance = Column(Numeric)

    # 在数据库中创建所有的定义的模型对应的表
    Base.metadata.create_all(engine)

except Exception as e:
    print(f"创建数据库表失败:{e}")

# 导入会话创建器
from sqlalchemy.orm import sessionmaker

# 创建会话工厂，绑定上面定义的数据库引擎
Session = sessionmaker(bind=engine)
# 创建会话对象
session = Session()
# new_account1 = Account(user_id=1, balance=200)
# new_account2 = Account(user_id=2, balance=0)
# session.add(new_account1)
# session.add(new_account2)
# session.commit()
# try:
#    zhang = session.query(Account).filter_by(user_id=1).first()
#    zhang.balance = zhang.balance - 100
#    li = session.query(Account).filter_by(user_id=4).first()
#    li.balance = li.balance + 100
#    # 如果所有的操作都成功了，则进行统一提交
#    session.commit()
# except:
#    # 如果有某个操作失败了，则全部回滚
#    session.rollback()
#    raise
zhang = session.query(User).filter_by(id=1).first()
print(zhang.description)
